import pandas as pd
from openpyxl import load_workbook
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

from rectify_idf.error1 import error1_idf
from rectify_idf.error10 import error10_idf
from rectify_idf.error11 import error11_idf
from rectify_idf.error12 import error12_idf
from rectify_idf.error2 import error2_idf
from rectify_idf.error3 import error3_idf
from rectify_idf.error4 import error4_idf
from rectify_idf.error5 import error5_idf
from rectify_idf.error6 import error6_idf

# 标准颜色库
COLOR_SCHEME = {
    'warning': PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid'),  # 黄色
    'error': PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid'),  # 红色
    'info': PatternFill(start_color='0070C0', end_color='0070C0', fill_type='solid'),  # 深蓝
    'valid': PatternFill(start_color='00B050', end_color='00B050', fill_type='solid'),  # 绿色
    'special': PatternFill(start_color='7030A0', end_color='7030A0', fill_type='solid')  # 紫色
}


def add_excel_formats(file_path, styles_info, comments_info, df):
    """应用样式和批注到Excel文件"""
    try:
        wb = load_workbook(file_path)
        ws = wb.active

        # 应用单元格样式
        for (row_idx, col_name), style_type in styles_info.items():# 遍历字典中的键值对
            col_idx = df.columns.get_loc(col_name)#获取列索引
            col_letter = get_column_letter(col_idx + 1)#获取列字母
            cell = ws[f"{col_letter}{row_idx + 2}"]#获取单元格对象
            cell.fill = COLOR_SCHEME.get(style_type, COLOR_SCHEME['warning'])#添加样式

        # 添加批注
        for row_idx, col_name, message in comments_info:
            col_idx = df.columns.get_loc(col_name)
            col_letter = get_column_letter(col_idx + 1)
            cell = ws[f"{col_letter}{row_idx + 2}"]
            if cell.comment:
                cell.comment.text += f"\n{message}"#添加批注
            else:
                cell.comment = Comment(message, "QC System")#批注

        wb.save(file_path)
        print(f"格式处理完成 | 样式: {len(styles_info)} | 批注: {len(comments_info)}")
    except Exception as e:
        print(f"格式处理失败: {str(e)}")


def main():
    # 初始化数据结构
    df = pd.read_excel('文档/随访数据.xlsx', engine='openpyxl')
    df.columns = df.columns.str.replace(r'[（）]', lambda x: '(' if x.group() == '（' else ')', regex=True)

    header = df.columns.tolist()
    print("表头:", header)

    # 初始化存储
    styles_info = {}  # {(行索引, 列名): 样式类型}
    comments_info = []  # [(行索引, 列名, 批注内容)]

    # dict_info_setting
    dict_info_setting = {}

    with open("文档/setting.txt", 'r', encoding='utf-8') as f1:
        for idx,line in enumerate(f1.readlines(),start=1):
            dict_info_setting[f"error{idx}_idf"] = line


    # 定义质控流程
    quality_controls = [
        (error1_idf, {"columns": ['收缩压(mmHg)', '舒张压(mmHg)', '此次随访分类', '身份证号']}),
        (error2_idf, {"columns": ['体重(kg)', '体重控制建议(公斤)', '体质指数']}),
        (error3_idf, {"columns": ['身份证号', '随访日期', '收缩压(mmHg)', '舒张压(mmHg)', '心率', '体重(kg)']}),
        (error4_idf,
         {"columns": ['空腹血糖(mmol/L)', '此次随访分类', '收缩压(mmHg)', '舒张压(mmHg)', '身份证号']}),
        (error5_idf, {"columns": ['足背动脉搏动', '随访医生签名']}),
        (error6_idf, {"columns": ['身份证号', '随访日期', '随访建议']}),
        (error10_idf,
         {"columns": ['吸烟量', '建议日吸烟量(支/天)', '日饮酒量(两)', '建议日饮酒量(两/天)',
                      '每周运动次数(次)', '每次运动时间(分钟/次)', '建议每周运动次数(次)', '建议每次运动时间(分钟/次)']}),
        (error11_idf,
         {"columns": ['日吸烟量(支)', '建议日吸烟量(支/天)', '日饮酒量(两)', '建议日饮酒量(两/天)',
                      '每周运动次数(次)', '每次运动时间(分钟/次)', '建议每周运动次数(次)',
                      '建议每次运动时间(分钟/次)', '主食类(克/天)', '建议主食类(克/天)']}),
        (error12_idf, {"columns": ['身高(cm)', '心率']}),
    ]

    # 执行质控流程
    for qc_func, params in quality_controls:

        if qc_func == error1_idf:
            if '空腹血糖(mmol/L)' in df.columns.tolist():
                print("糖尿病随访 不处理高血压--此次随访分类--逻辑")
                continue

        if qc_func == error3_idf:
            if '随访日期' not in df.columns.tolist():
                print("高血压随访 不处理糖尿病--随访数据相邻两次相同--逻辑")
                continue

        if qc_func == error4_idf:
            if '空腹血糖(mmol/L)' not in df.columns.tolist():
                print("高血压随访 不处理糖尿病--此次随访分类--逻辑")
                continue
        if qc_func == error5_idf:
            if '足背动脉搏动' not in df.columns.tolist():
                print("高血压随访 不处理糖尿病--足背动脉搏动--逻辑")
                continue
        if qc_func == error6_idf:
            if '随访日期' not in df.columns.tolist():
                print("高血压随访 不处理糖尿病--随访建议相邻两次相同--逻辑")
                continue
        if qc_func == error10_idf:
            if '吸烟量' not in df.columns.tolist():
                print("糖尿病随访 不处理高血压--吸烟、饮酒、锻炼--逻辑")
                continue
        if qc_func == error11_idf:
            if '主食类(克/天)' not in df.columns.tolist():
                print("高血压随访 不处理糖尿病--吸烟、饮酒、锻炼、主食--逻辑")
                continue

        new_styles, new_comments = qc_func(df,dict_info_setting, **params)# 执行质控函数
        styles_info.update(new_styles)
        comments_info.extend(new_comments)
        print(f"[{qc_func.__name__}] 处理完成 | 发现异常: {len(new_comments)}处")

    # 在 output_path = '执行结果/随访数据_纠错.xlsx' 之后添加以下代码

    # 保存基础数据
    output_path = '执行结果/随访数据_纠错.xlsx'

    # 统计各类样式信息
    style_counts = {'warning': 0, 'error': 0, 'info': 0, 'valid': 0, 'special': 0}
    for _, style_type in styles_info.items():
        if style_type in style_counts:
            style_counts[style_type] += 1

    # 创建统计DataFrame
    stats_data = {
        '样式类型': ['warning', 'error', 'info', 'valid', 'special'],
        '数量': [style_counts['warning'], style_counts['error'], style_counts['info'], style_counts['valid'],
                 style_counts['special']]
    }
    stats_df = pd.DataFrame(stats_data)

    # 创建按列分组的详细统计信息
    # 初始化统计字典
    column_stats = {col: {'warning': 0, 'error': 0, 'info': 0, 'valid': 0, 'special': 0} for col in df.columns}

    # 统计每列的各类问题数量
    for (row_idx, col_name), style_type in styles_info.items():
        if col_name in column_stats and style_type in column_stats[col_name]:
            column_stats[col_name][style_type] += 1

    # 构造详细统计DataFrame
    detailed_stats_data = []
    # for col_name in df.columns:
    #     stats = column_stats[col_name]
    #     # 只添加有任意类型问题的列，或者添加所有列（根据需要可调整）
    #     if any(count > 0 for count in stats.values()):
    #         detailed_stats_data.append({
    #             '字段名': col_name,
    #             'warning': stats['warning'],
    #             'error': stats['error'],
    #             'info': stats['info'],
    #             'valid': stats['valid'],
    #             'special': stats['special']
    #         })

    # 如果需要显示所有列（包括没有问题的列），使用以下代码替代上面的循环：
    for col_name in df.columns:
        stats = column_stats[col_name]
        detailed_stats_data.append({
            '字段名': col_name,
            'warning': stats['warning'],
            'error': stats['error'],
            'info': stats['info'],
            'valid': stats['valid'],
            'special': stats['special']
        })

    detailed_stats_df = pd.DataFrame(detailed_stats_data)

    # 保存基础数据
    df.to_excel(output_path, index=False, engine='openpyxl')

    # 将统计信息追加到Excel文件中
    with pd.ExcelWriter(output_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        stats_df.to_excel(writer, sheet_name='样式统计', index=False)
        detailed_stats_df.to_excel(writer, sheet_name='详细统计', index=False)

    # 应用格式和批注
    add_excel_formats(output_path, styles_info, comments_info, df)


if __name__ == "__main__":
    main()

